﻿CREATE PROCEDURE [dbo].[proc_CallCenter_Getlist]
	(
		@CompanyId int,
		@DepartmentId int,
		@StartIndex int,
		@EndIndex int,
		@Stext nvarchar(50),
		@telFlag varchar(1)
	)
AS
Begin
	Declare @cSql nvarchar(4000),@cWhere nvarchar(500)

	If @CompanyId = 0
		Set @cWhere = 'CompanyId In(1,22,50)'
	Else
		Set @cWhere = 'CompanyId = ' + Convert(varchar(10),@CompanyId)

	If @DepartmentId > 0
		Set @cWhere = @cWhere + ' And DepartmentId in (Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T)'

	If @Stext<>''
	Begin
		If @telFlag='1'
			Set @cWhere = @cWhere + ' And Cast(FJNum As varchar(10))='''+@Stext+''''
		Else
			Set @cWhere = @cWhere + ' And EName like ''%'+@Stext+'%'''
	End

	Set @cSql = ''
	If @DepartmentId > 0
		Set @cSql = 'Declare @bmbh_T  varchar(50)
	Select @bmbh_T=bmbh From department Where id=' + Convert(varchar(10),@departmentid)

	Set @cSql = @cSql + '
	Declare @dRq DateTime
	Set @dRq = Convert(DateTime,Convert(varchar(4),Year(GetDate())) + ''-'' + Convert(varchar(4),Month(GetDate())) + ''-1'')

	;WITH list as (Select ROW_NUMBER() OVER (ORDER BY ZXNum)AS Row,
		Id,
		GId,
		Isnull(CompanyId,0) As CompanyId,
		Isnull(DepartmentId,0) As DepartmentId,
		UserName,
		EName,
		ZXNum,
		FJNum
		From CallCenter Where ' + @cWhere + '
	)
	
	Select *,(SELECT COUNT(0) FROM list) AS coun,
		(Select  CompanyName From Company Where Id=list.CompanyId) As Company,
		(Select  DName From Department Where Id=list.DepartmentId) As Department,
		(Select Count(0) From CallCenter_Record Where EndDate>=@dRq And ZuoxiID=list.ZXNum And CompanyId=list.CompanyId) As CallNum
	From list Where Row Between ' + Convert(varchar(10),@StartIndex) + ' And ' + Convert(varchar(10),@EndIndex) + ' ORDER BY row'

	Exec(@cSql)
End
